package org.prophet.accessexcel.service;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.prophet.accessexcel.entities.Result;
import org.prophet.accessexcel.utility.ExcelOperater;
import org.prophet.accessexcel.utility.ReflectUtils;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.ListIterator;
import java.util.logging.Logger;

/**
 * @Description: 实现ExcelCRUD4Sheet接口
 * @author: april
 * @date: 2020年10月13日 22:21
 */
public class ExcelCRUD4SheetImpl<T> implements ExcelCRUD4Sheet<T> {

    private Workbook workbook;
    private Logger logger = Logger.getLogger(ExcelCRUD4SheetImpl.class.getName());
    private Class<T> tClass;
    private File file;

    public ExcelCRUD4SheetImpl(String fileName, Class<T> clazz) {
        try {
            workbook = ExcelOperater.getWorkbook(fileName);
            this.tClass = clazz;
            this.file = new File(fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public ExcelCRUD4SheetImpl(File file, Class<T> clazz) {
        String fileName = file.getPath();
        String suffix = fileName.substring(fileName.indexOf(".") + 1);
        if (ExcelOperater.SUFFIX_XLS.equalsIgnoreCase(suffix) || ExcelOperater.SUFFIX_XLSX.equalsIgnoreCase(suffix)) {
            try {
                workbook = ExcelOperater.getWorkbook(file, suffix);
                this.tClass = clazz;
                this.file = file;
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 获取指定sheetNum的sheet对象
     *
     * @param sheetNum
     * @return
     */
    private Sheet getSheet(int sheetNum) {
        return workbook.getSheetAt(sheetNum);
    }

    public List<T> queryAll4Sheet(Sheet sheet) {
        List<T> results = new ArrayList<>();
        // 获取第一行数据
        int firstRowNum = sheet.getFirstRowNum();

        // 解析每一行的数据，构造数据对象
        int rowStart = firstRowNum + 1;
        int rowEnd = sheet.getPhysicalNumberOfRows();
        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
            Row row = sheet.getRow(rowNum);

            if (row == null) {
                continue;
            }

            T resultData = null;
            try {
                resultData = ExcelOperater.convertRowToData(row, tClass.newInstance());
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            if (null == resultData) {
                logger.warning("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                continue;
            }
            results.add(resultData);
        }
        return results;
    }

    /**
     * 根据sheet的编号查询对应sheet中的所有记录
     *
     * @param sheetNum
     * @return
     */
    @Override
    public List<T> queryAll4Sheet(int sheetNum) {
        Sheet sheet = getSheet(sheetNum);

        return queryAll4Sheet(sheet);
    }

    @Override
    public List<T> queryAll4Sheet() {
        //Sheet sheet=ExcelOperater.getSheet4Workbook(workbook);
        return queryAll4Sheet(0);
    }

    @Override
    public List<T> queryAll4Excel() {
        List<Sheet> sheets = ExcelOperater.getSheets4Workbook(workbook);
        List<T> results = new ArrayList<>();
        // 解析excel中所有sheet
        for (int sheetNum = 0; sheetNum < sheets.size(); sheetNum++) {
            //Sheet sheet = sheets.get(sheetNum);
            //把每页sheet中的列表对象重新添加到results中
            ListIterator<T> listIterator = queryAll4Sheet(sheetNum).listIterator();
            while (listIterator.hasNext()) {
                results.add(listIterator.next());
            }
        }
        return results;
    }

    /**
     * 通过行号查询并返回对应对象
     *
     * @param sheetNum 表下标从0开始
     * @param rowId    行号从0开始
     * @return
     */
    @Override
    public T queryByRowId4Sheet(int sheetNum, int rowId) {
        //List<T> listSheet=queryAll4Sheet(sheet);
        Sheet sheet = getSheet(sheetNum);

        if (rowId >= 0) {
            Row row = sheet.getRow(rowId + 1);
            if (row == null) {
                return null;
            }
            try {
                return ExcelOperater.convertRowToData(row, tClass.newInstance());
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        } else {
            try {
                throw new Exception("参数rowId应大于0;rowId为0显示列名.");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public T queryByRowId4Sheet(int rowId) {
        return queryByRowId4Sheet(0, rowId);
    }

    /**
     * 通过提供属性名和属性值，检索sheet中所有满足条件的记录
     *
     * @param sheetNum   sheet下标
     * @param fieldName  属性名
     * @param fieldValue 值
     * @return
     */
    @Override
    public List<T> queryByFieldValue4Sheet(int sheetNum, String fieldName, Object fieldValue) {
        List<T> results = new ArrayList<>();
        List<T> list41Sheet = queryAll4Sheet(sheetNum);
        List<String> columns = getColumnNames(sheetNum);
        for (T t : list41Sheet) {

            //if(fieldName.equalsIgnoreCase(col)){ //sheet列名和对象属性名不一样，也无需判断
            Object obj = null;
            try {
                obj = ReflectUtils.getInvoke4GetMethod(t, fieldName);
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            if (fieldValue.equals(obj)) {
                results.add(t);
            }
        }
        if (results.size() > 0) {
            return results;
        }
        return null;
    }

    public List<T> queryByFieldValue(String fieldName, Object fieldValue) throws InvocationTargetException, IllegalAccessException {
        return queryByFieldValue4Sheet(0, fieldName, fieldValue);
    }

    /**
     * 获取对应sheet的列名
     *
     * @param sheetNum
     * @return
     */
    @Override
    public List<String> getColumnNames(int sheetNum) {
        Sheet sheet = getSheet(sheetNum);
        int firstRowNum = sheet.getFirstRowNum();
        Row firstRow = sheet.getRow(firstRowNum);
        if (null == firstRow) {
            logger.warning("解析Excel失败，在第一行没有读取到任何数据！");
            return null;
        }
        //列名应该在每个sheet的第一行
        List<String> colNameList = new ArrayList<>();
        Iterator cellIterator = firstRow.cellIterator();
        while (cellIterator.hasNext()) {
            colNameList.add(cellIterator.next().toString());
        }
        if (0 == colNameList.size()) {
            logger.warning("解析Excel失败，无法从第一行获取字段名称！");
            return null;
        }
        return colNameList;
    }

    /**
     * 在当前sheet末尾插入一行记录，并保存结果到磁盘文件
     *
     * @param sheetNum
     * @param t
     * @return
     */
    @Override
    public Result appendRow2Sheet(int sheetNum, T t) {
        Result result = null;
        Sheet sheet = getSheet(sheetNum);
        //插入数据前，验证t的主键是否唯一
        try {
            result = checkRepetition(sheet, t, "id");
            if (result.getResponseCode() == -1) {
                return result;
            }
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        //在现有记录的末尾插入
        int appendIdx = lastRowId(sheetNum) + 1;

        Row row = sheet.createRow(appendIdx);
        result = modifyRowInSheet(t, row, sheet);
        return result;
    }

    //返回记录行号，从0开始
    public int lastRowId(int sheetNum) {
        List<T> rows = queryAll4Sheet(sheetNum);
        //通过getPhysicalNumberOfRows获取的行下标比返回到list中的记录数多1
        //getPhysicalNumberOfRows返回当前sheet中有记录的最后一行行号
        //int rowEnd = getSheet(sheetNum).getPhysicalNumberOfRows();

        return rows.size();
    }

    public int lastRowId() {
        return lastRowId(0);
    }

    public int lastPhysicalRowId(int sheetNum) {
        Sheet sheet = getSheet(sheetNum);
        return sheet.getPhysicalNumberOfRows();
        //return sheet.getLastRowNum();
    }

    /**
     * 根据提供的对象和主键名，返回sheet中的物理行号
     *
     * @param t      对象
     * @param pkName 主键，唯一性属性名
     * @param rows   sheet中的行集合
     * @return sheet中的物理行号
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    public int findRowIdByObj(T t, String pkName, List<T> rows) throws InvocationTargetException, IllegalAccessException {
        Object pkValue = ReflectUtils.getInvoke4GetMethod(t, pkName);
        return findRowIdByValue(pkValue, pkName, rows);
    }

    /**
     * 根据提供的主键pk字段值返回对应物理行号
     *
     * @param value
     * @param pkName
     * @param rows
     * @return
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    public int findRowIdByValue(Object value, String pkName, List<T> rows) throws InvocationTargetException, IllegalAccessException {
        for (int i = 0; i < rows.size(); i++) {
            if (value.equals(ReflectUtils.getInvoke4GetMethod(rows.get(i), pkName))) {
                return i + 1;
            }
        }
        return -1;
    }


    /**
     * 未实现，勿使用
     *
     * @param sheetNum
     * @param t
     * @param rowId    插入行的位置
     * @return
     */
    @Override
    public Result insertRow4Sheet(int sheetNum, T t, int rowId) {
        Sheet sheet = getSheet(sheetNum);
        Result<T> result = new Result<>();
        if (rowId < 1 || rowId > lastRowId(sheetNum) + 1) {
            result.setResponseCode(-1);
            result.setMessage("数据插入行号越界.");
            return result;
        }
        if (rowId == lastRowId(sheetNum) + 1) {
            result = appendRow2Sheet(sheetNum, t);
        }
        //非最后一行插入,未实现
        //类似数组插入，把index之后的所有记录后移，然后在index位置插入t
        /*try {
            result = checkRepetition(sheet, t, "id");
            if (result.getResponseCode() == -1) {
                return result;
            }
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        if (sheet.getRow(rowId) != null) {
            int lastIndex = sheet.getLastRowNum();
            //应先定义lastIndex，如果直接把sheet.getLastRowNum()作为参数，会出现每移动一行，最后一列的下标变化的情况
            sheet.shiftRows(rowId, lastIndex, 1);
        }
        Row row = sheet.createRow(rowId);
        result = modifyRowInSheet(t, row, sheet);*/

        return result;
    }

    /**
     * 根据行号跟新sheet中的记录
     *
     * @param sheetNum
     * @param t
     * @param rowId    行记录下标，值=物理行号-1
     * @return
     */
    @Override
    public Result updateRowByRowId4Sheet(int sheetNum, T t, int rowId) {
        Sheet sheet = getSheet(sheetNum);
        Result<T> result = new Result<>();
        if (rowId > lastRowId(sheetNum)) {
            result.setResponseCode(-1);
            result.setMessage("数据更新下标越界.");
            return result;
        }
        Row row = sheet.getRow(rowId);
        result = modifyRowInSheet(t, row, sheet);

        return result;
    }

    private Result modifyRowInSheet(T t, Row row, Sheet sheet) {
        boolean bl = false;
        try {
            bl = ExcelOperater.convertDataToRow(t, row);
            if (bl) {
                ExcelOperater.write2ExcelSerialized(file, sheet);
            }
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return bl ? new Result(1, "success", t) : new Result<>(-1, "数据修改异常.", t);
    }

    /**
     * 根据指定的rowid删除行,暂时实现删除最末行
     *
     * @param sheetNum
     * @param rowId
     * @return
     */
    @Override
    public int deleteRowByRowId4Sheet(int sheetNum, int rowId) {
        Sheet sheet = getSheet(sheetNum);
        int lastId = lastRowId(sheetNum);
        if (rowId < 1 || rowId > lastId) {
            return -1;
        }
        //强制指定行id为最后一行
        rowId=lastId;
        if (rowId == lastId) {
            Row row = sheet.getRow(rowId);
            sheet.removeRow(row);
        } /*else {
            sheet.shiftRows(rowId + 1, lastId, -1, true, false);
        }*/

        try {
            ExcelOperater.write2ExcelSerialized(file, sheet);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return lastId - 1;
    }

    /**
     * sheet主键pk不允许出现重复记录，一般指id必须唯一
     *
     * @param sheet
     * @param t
     * @param pkName
     * @return ResponseCode 1表示无重复，-1表示重复
     */
    private Result<T> checkRepetition(Sheet sheet, T t, String pkName) throws InvocationTargetException, IllegalAccessException {
        Object pkOut = ReflectUtils.getInvoke4GetMethod(t, pkName);
        Result result = new Result(1, "success", t);
        List<T> vos = queryAll4Sheet(sheet);
        for (T vo : vos) {
            Object pkIn = ReflectUtils.getInvoke4GetMethod(vo, pkName);
            if (pkOut.equals(pkIn)) {
                result.setMessage("主键不唯一，无法执行插入操作.");
                result.setResponseCode(-1);
                return result;
            }
        }
        return result;
    }
}
